<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
//Apti财务管理
class Apticaiwu extends Common
{
	//导出公用
	public function daochu($datas,$title){
			$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:H2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

         $objPHPExcel->getActiveSheet()->mergeCells('A3:H3');
        $objPHPExcel->getActiveSheet()->setCellValue('A3',"单位：元");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(11);


         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	    $objPHPExcel->getActiveSheet()->setCellValue('A4',"年度");
        $objPHPExcel->getActiveSheet()->setCellValue('B4',"预提总额");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"核销总额");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"支付总额");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"预付总额");      
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"预付转支付总额");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"预付余额");
        $objPHPExcel->getActiveSheet()->setCellValue('H4',"预提余额");

        $h=4;
        for($i=0;$i<count($datas);$i++){
        	$h++;$tmp=$datas[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$tmp["title"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$tmp["yuti"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$tmp["hexiao"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["zhifu"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$tmp["zongyu"]);      
	        $objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["zhuan"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["shengyu"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$tmp["ytye"]);


        }
        $hj=$h+1;
         $objPHPExcel->getActiveSheet()->getStyle($hj)->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':H'.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	     $objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
	     $objPHPExcel->getActiveSheet()->setCellValue('B'.$hj, '=SUM(B5:B'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('C'.$hj, '=SUM(C5:C'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('D'.$hj, '=SUM(D5:D'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('E'.$hj, '=SUM(E5:E'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('F'.$hj, '=SUM(F5:F'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('G'.$hj, '=SUM(G5:G'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('H'.$hj, '=SUM(H5:H'.$h.')');


           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);





	        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:H'.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        





        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename='.$title.'.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');

	}
	//总月度对账导出
	public function monthdaochu($kaohe2){
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("c.id as cid,k1.id as k1id,k2.id as k2id,c.title as ctitle,k1.title as k1title,k2.title as k2title,k1.starttime as k1start,k1.endtime as k1end,k2.starttime as k2start,k2.endtime as k2end")
			->find();
			$data["kaohe2"]=$kaohe2;$data["kaohe1"]=$nowt["k1id"];$data["cainian"]=$nowt["cid"];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];$k2title=$nowt["k2title"];
			$kaohe1days=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		$kaohe2days=($nowt["k2end"]-$nowt["k2start"])/3600/24+1;
		$startdate=date("Y-m-d",$nowt["k2start"]);
		$enddate=date("Y-m-d",$nowt["k2end"]+3600*24);
		//echo $kaohe2days;
		//Ap累计预提
		$Apleiji=Db::table("ap_apply")->alias("ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")->where("ap.state=7 and ap.is_shengxiao=1 and ap.tag=0")->sum("apd.pifuzonge+apd.pifuzonge*apd.shuilv/100");
		
		//Ti累计预提
		$Tileiji=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")->where("ap.status=7 and (ap.status2=0 or ap.status2 is null)")->sum("apd.yjsjtrfy");
		
		//echo $Tileiji;
		//当月AP/Ti预提
		$Aptmp=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id in (2,3) and (apd.erjikaoheqi={$data["kaohe2"]} or (apd.yijikaoheqi={$data["kaohe1"]} and apd.erjikaoheqi is null))")
		->field("cainian,yijikaoheqi,erjikaoheqi,feiyong,ap.type_id")
		->select();
		//$thismonth=Date("Y-m");
		
		$thismonth=date("Y-m-d H:i:s",$nowt['k2start']);
		$thismonthst=$nowt['k2start'];
		$Tidangyue=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")->where("ap.status=7 and (ap.status2=0 or ap.status2 is null) and ap.ctime>=$thismonthst")->sum("apd.yjsjtrfy");
		
		//$thismonth=Date("Y-m");
		$Apdangyue=Db::table("ap_apply")->alias("ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")->where("ap.state=7 and ap.is_shengxiao=1 and ap.tag=0 and ap.created_on>='{$thismonth}'")->sum("apd.pifuzonge+apd.pifuzonge*apd.shuilv/100");
		$Apdangyue=round($Apdangyue,2);$Tidangyue=round($Tidangyue,2);
		//echo Db::table("ap_apply")->getLastSql();
		
	
		//累积剩余预付
		$apshengyu=0;
		//累积预付转支付
		$apzhuan=0;
		//累积预付
		$apyufu=0;
			//Ap累计核销
		$Apallhexiao=0;
		//当月ap核销
		$dangyueaphe=0;
		//当月预付
		$dangyueapyu=0;
		//核销记录
		$aphxrecord=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
	
		->where("ahp.is_deleted=0 and ahp.state in (7)")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();
		//print_r($aphxrecord);
		for($i=0;$i<count($aphxrecord);$i++){
			$tmpap=$aphxrecord[$i];
			$Apallhexiao+=$tmpap["hanshui"];
			if(!$tmpap["fp_no"]){
				$apshengyu+=$tmpap["hanshui"];
				
			}
			if($nowt["k2start"]<=strtotime($tmpap["created_on"])&&$nowt["k2end"]+3600*24>strtotime($tmpap["created_on"])){
				$dangyueaphe+=$tmpap["hanshui"];
				if($tmpap["bu_state"]==2||!$tmpap["fp_no"]){
					$dangyueapyu+=$tmpap["hanshui"];
				}
			}

		}
		$dangyueapyu=round($dangyueapyu,2);
		
		$dangyueaphe=round($dangyueaphe,2);
		
		$Apallhexiao=round($Apallhexiao,2);
		
		$apzhuan=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		
		->where("ahp.is_deleted=0 and ahp.state in (7)  and ahd.has_fp=1 and ahd.bu_state=2")
		->sum("ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100");
		//echo $apzhuan;
		$apyufu=$apzhuan+$apshengyu;
		$apshengyu=round($apshengyu,2);
		$apzhuan=round($apzhuan,2);
		$apyufu=round($apyufu,2);
	
		//当月预付转支付
		$apdangyuezhuan=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
	
		->where("ahp.is_deleted=0 and ahp.state in (7)  and ahd.has_fp=1 and ahp.created_on>='{$startdate}' and ahp.created_on<'{$enddate}' and ahd.bu_state=2")
		->sum("ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100");
		//echo Db::table("ap_hexiao_apply")->getLastSql();
		$apdangyuezhuan=round($apdangyuezhuan,2);
		
		$dangyuapyy=round($dangyueapyu-$apdangyuezhuan,2);
		
	
		//Ap累积预提余额
		$apleijiyue=$Apleiji-$Apallhexiao;
		$apleijiyue=round($apleijiyue,2);
		
		
		//当月ap支付
		$apdangzf=round(Db::table("ap_zhifu")->where("zhifu_day>='{$startdate}' and zhifu_day<'{$enddate}'")->sum("zfjin"),2);
		
		//Ap所有具体支付情况
		$apallzfde=Db::table("ap_zhifu")
		->select();
		//print_r($apallzfde);
		//总ap支付
		$apallzf=0;
		
		for($i=0;$i<count($apallzfde);$i++){
			$thiszf=$apallzfde[$i];
			$apallzf+=$thiszf["zfjin"];
			
		}
		$apallzf=round($apallzf,2);

		
		//当月ap预提剩余
		$apdangyueyts=round($Apdangyue-$dangyueaphe,2);
		

			//Ti核销
		$Tiallhexiao=0;$dangtihexiao=0;

		$alltihede=Db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.hanshuihexiaojine,ahp.ctime")->select();
		for($i=0;$i<count($alltihede);$i++){
			$Tiallhexiao+=$alltihede[$i]["hanshuihexiaojine"];
			if($alltihede[$i]["ctime"]>=$nowt["k2start"]&&$alltihede[$i]["ctime"]<$nowt["k2end"]+3600*24){
				$dangtihexiao+=$alltihede[$i]["hanshuihexiaojine"];
			}

			
		}

		$dangtihexiao=round($dangtihexiao,2);
		//print_r($Tiallhexiao);exit;
		$Tiallhexiao=round($Tiallhexiao,2);
		//Ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->field("bczfje,zfsj")->select();
		//累积支付
		$tiallzf=0;
		//当月支付
		$tidangyuezf=0;
		for($i=0;$i<count($tizf);$i++){
			$tiallzf+=$tizf[$i]["bczfje"];
			if($nowt["k2start"]<=strtotime($tizf[$i]["zfsj"])&&$nowt["k2end"]+3600*24>strtotime($tizf[$i]["zfsj"]))
				$tidangyuezf+=$tizf[$i]["bczfje"];
		}
		$tidangyuezf=round($tidangyuezf,2);
		
		$tiallzf=round($tiallzf,2);
		
		//Ti全部核销明细
		$tihexiaolist=Db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine")
		->select();
		$tiallsy=0;$tiallzhuan=0;
		for($i=0;$i<count($tihexiaolist);$i++){
			if($tihexiaolist[$i]["youwufapiao"]==0||!$tihexiaolist[$i]["fapiaohao"])
				$tiallsy+=$tihexiaolist[$i]["hanshuihexiaojine"];
			if($tihexiaolist[$i]["bu_state"]==2)
				$tiallzhuan+=$tihexiaolist[$i]["hanshuihexiaojine"];
		}
		$tiallsy=round($tiallsy,2);
		
		$tiallzhuan=round($tiallzhuan,2);
		
		$tially=round($tiallsy+$tiallzhuan,2);
		
		$nowend=$nowt["k2end"]+3600*24;
		//ti当月核销明细
		$tidanglist=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9) and ahp.ctime>={$nowt["k2start"]} and ahp.ctime<{$nowend}")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine")->select();
		$tidangsy=0;$tidangzhuan=0;
		for($i=0;$i<count($tidanglist);$i++){
			if($tidanglist[$i]["youwufapiao"]==0||!$tidanglist[$i]["fapiaohao"])
				$tidangsy+=$tidanglist[$i]["hanshuihexiaojine"];
			if($tidanglist[$i]["bu_state"]==2)
				$tidangzhuan+=$tidanglist[$i]["hanshuihexiaojine"];
		}
		$tidangzhuan=round($tidangzhuan,2);
		
		$tidangsy=round($tidangsy,2);
		
		$tidangy=round($tidangzhuan+$tidangsy,2);
		
		//ti累计剩余预提
		$tileijisyyt=round($Tileiji-$Tiallhexiao,2);
		
		//ti当月剩余预提
		$tidangsyyt=round($Tidangyue-$dangtihexiao,2);
		
		//$this->assign("tidangsyyt",$tidangsyyt);
				$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("月度对账单");
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "月度对账单");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

         $objPHPExcel->getActiveSheet()->mergeCells('A3:P3');
        $objPHPExcel->getActiveSheet()->setCellValue('A3',"单位：元");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(11);


         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A4:P4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A4:P4')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A4:P4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A4:P4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	    $objPHPExcel->getActiveSheet()->setCellValue('A4',"月份");
        $objPHPExcel->getActiveSheet()->setCellValue('B4',"类型");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"当月预提");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"累积预提");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"当月核销");      
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"累积核销");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"当月支付");
        $objPHPExcel->getActiveSheet()->setCellValue('H4',"累积支付");
        $objPHPExcel->getActiveSheet()->setCellValue('I4',"当月预付");
        $objPHPExcel->getActiveSheet()->setCellValue('J4',"累积预付");
        $objPHPExcel->getActiveSheet()->setCellValue('K4',"当月预付转支付");
        $objPHPExcel->getActiveSheet()->setCellValue('L4',"累积预付转支付");
        $objPHPExcel->getActiveSheet()->setCellValue('M4',"当月预付余额");
        $objPHPExcel->getActiveSheet()->setCellValue('N4',"累积预付余额");
        $objPHPExcel->getActiveSheet()->setCellValue('O4',"当月预提余额");
        $objPHPExcel->getActiveSheet()->setCellValue('P4',"累积预提余额");
         $objPHPExcel->getActiveSheet()->getStyle('5')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('6')->getAlignment()->setWrapText(true);
      	   $objPHPExcel->getActiveSheet()->getStyle('A5:P6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A5:P6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	    $objPHPExcel->getActiveSheet()->setCellValue('A5',"{$ctitle}-{$k2title}");
        $objPHPExcel->getActiveSheet()->setCellValue('B5',"TI");
        $objPHPExcel->getActiveSheet()->setCellValue('C5',$Tidangyue);
        $objPHPExcel->getActiveSheet()->setCellValue('D5',$Tileiji);
        $objPHPExcel->getActiveSheet()->setCellValue('E5',$dangtihexiao);      
        $objPHPExcel->getActiveSheet()->setCellValue('F5',$Tiallhexiao);
        $objPHPExcel->getActiveSheet()->setCellValue('G5',$tidangyuezf);
        $objPHPExcel->getActiveSheet()->setCellValue('H5',$tiallzf);
        $objPHPExcel->getActiveSheet()->setCellValue('I5',$tidangy);
        $objPHPExcel->getActiveSheet()->setCellValue('J5',$tially);
        $objPHPExcel->getActiveSheet()->setCellValue('K5',$tidangzhuan);
        $objPHPExcel->getActiveSheet()->setCellValue('L5',$tiallzhuan);
        $objPHPExcel->getActiveSheet()->setCellValue('M5',$tidangsy);
        $objPHPExcel->getActiveSheet()->setCellValue('N5',$tiallsy);
        $objPHPExcel->getActiveSheet()->setCellValue('O5',$tidangsyyt);
        $objPHPExcel->getActiveSheet()->setCellValue('P5',$tileijisyyt);

        $objPHPExcel->getActiveSheet()->setCellValue('A6',"{$ctitle}-{$k2title}");
        $objPHPExcel->getActiveSheet()->setCellValue('B6',"AP");
        $objPHPExcel->getActiveSheet()->setCellValue('C6',$Apdangyue);
        $objPHPExcel->getActiveSheet()->setCellValue('D6',$Apleiji);
        $objPHPExcel->getActiveSheet()->setCellValue('E6',$dangyueaphe);      
        $objPHPExcel->getActiveSheet()->setCellValue('F6',$Apallhexiao);
        $objPHPExcel->getActiveSheet()->setCellValue('G6',$apdangzf);
        $objPHPExcel->getActiveSheet()->setCellValue('H6',$apallzf);
        $objPHPExcel->getActiveSheet()->setCellValue('I6',$dangyueapyu);
        $objPHPExcel->getActiveSheet()->setCellValue('J6',$apyufu);
        $objPHPExcel->getActiveSheet()->setCellValue('K6',$apdangyuezhuan);
        $objPHPExcel->getActiveSheet()->setCellValue('L6',$apzhuan);
        $objPHPExcel->getActiveSheet()->setCellValue('M6',$dangyuapyy);
        $objPHPExcel->getActiveSheet()->setCellValue('N6',$apshengyu);
        $objPHPExcel->getActiveSheet()->setCellValue('O6',$apdangyueyts);
        $objPHPExcel->getActiveSheet()->setCellValue('P6',$apleijiyue);

         $objPHPExcel->getActiveSheet()->getStyle('7')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A7:P7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A7:P7')->getFill()->getStartColor()->setARGB('00FFCC99');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A7:P7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A7:P7')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	      $objPHPExcel->getActiveSheet()->setCellValue('A7',"总计");
	       $objPHPExcel->getActiveSheet()->mergeCells('A7:B7');
	    $objPHPExcel->getActiveSheet()->setCellValue('C7','=SUM(C5:C6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('D7','=SUM(D5:D6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('E7','=SUM(E5:E6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('F7','=SUM(F5:F6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('G7','=SUM(G5:G6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('H7','=SUM(H5:H6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('I7','=SUM(I5:I6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('J7','=SUM(J5:J6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('K7','=SUM(K5:K6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('L7','=SUM(L5:L6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('M7','=SUM(M5:M6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('N7','=SUM(N5:N6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('O7','=SUM(O5:O6)');
	    $objPHPExcel->getActiveSheet()->setCellValue('P7','=SUM(P5:P6)');

           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(30);




	        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:P7')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        



        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=月度对账单.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');

	}
	//总月度对账
	public function monthduizhang(){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->order("starttime")->select();
		$kaohe2=Db::table("cg_kaoheqi2")->order("starttime")->select();
		$this->assign("cainian",$cainian);
		$this->assign("kaohe1",$kaohe1);
		$this->assign("kaohe2",$kaohe2);
		if($data){
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("c.id={$data["cainian"]} and k1.id={$data["kaohe1"]} and k2.id={$data["kaohe2"]}")
			->field("c.id as cid,k1.id as k1id,k2.id as k2id,c.title as ctitle,k1.title as k1title,k2.title as k2title,k1.starttime as k1start,k1.endtime as k1end,k2.starttime as k2start,k2.endtime as k2end")
			->find();
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];$k2title=$nowt["k2title"];
			//print_r($nowt);
		}else{
			$nowtime=date("Y-m-d");
			$now=strtotime($nowtime);
			$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.starttime<=$now and k2.endtime>=$now")
			->field("c.id as cid,k1.id as k1id,k2.id as k2id,c.title as ctitle,k1.title as k1title,k2.title as k2title,k1.starttime as k1start,k1.endtime as k1end,k2.starttime as k2start,k2.endtime as k2end")
			->find();

			$data=["cainian"=>$nowt["cid"],"kaohe1"=>$nowt["k1id"],"kaohe2"=>$nowt["k2id"]];
			$ctitle=$nowt["ctitle"];$k1title=$nowt["k1title"];$k2title=$nowt["k2title"];

		}
		$this->assign("ctitle",$ctitle);$this->assign("k1title",$k1title);$this->assign("k2title",$k2title);
		$kaohe1days=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		$kaohe2days=($nowt["k2end"]-$nowt["k2start"])/3600/24+1;
		$startdate=date("Y-m-d",$nowt["k2start"]);
		$enddate=date("Y-m-d",$nowt["k2end"]+3600*24);
		//echo $kaohe2days;
		//Ap累计预提
		$Apleiji=Db::table("ap_apply")->alias("ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")->where("ap.state=7 and ap.is_shengxiao=1 and ap.tag=0")->sum("apd.pifuzonge+apd.pifuzonge*apd.shuilv/100");
		$this->assign("Apleiji",$Apleiji);
		//Ti累计预提
		$Tileiji=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")->where("ap.status=7 and (ap.status2=0 or ap.status2 is null)")->sum("apd.yjsjtrfy");
		$this->assign("Tileiji",$Tileiji);
		//echo $Tileiji;
		//当月AP/Ti预提
		$Aptmp=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id in (2,3) and (apd.erjikaoheqi={$data["kaohe2"]} or (apd.yijikaoheqi={$data["kaohe1"]} and apd.erjikaoheqi is null))")
		->field("cainian,yijikaoheqi,erjikaoheqi,feiyong,ap.type_id")
		->select();
		//$thismonth=Date("Y-m");
		
		$thismonth=date("Y-m-d H:i:s",$nowt['k2start']);
		$thismonthst=$nowt['k2start'];
		$Tidangyue=Db::table("cg_ti")->alias("ap")->join("cg_ti_list apd","apd.ti_id=ap.id")->where("ap.status=7 and (ap.status2=0 or ap.status2 is null) and ap.ctime>=$thismonthst")->sum("apd.yjsjtrfy");
		
		//$thismonth=Date("Y-m");
		$Apdangyue=Db::table("ap_apply")->alias("ap")->join("ap_apply_detail apd","apd.apply_id=ap.id")->where("ap.state=7 and ap.is_shengxiao=1 and ap.tag=0 and ap.created_on>='{$thismonth}'")->sum("apd.pifuzonge+apd.pifuzonge*apd.shuilv/100");
		$Apdangyue=round($Apdangyue,2);$Tidangyue=round($Tidangyue,2);
		//echo Db::table("ap_apply")->getLastSql();
		$this->assign("Apdangyue",$Apdangyue);$this->assign("Tidangyue",$Tidangyue);
	
		//累积剩余预付
		$apshengyu=0;
		//累积预付转支付
		$apzhuan=0;
		//累积预付
		$apyufu=0;
			//Ap累计核销
		$Apallhexiao=0;
		//当月ap核销
		$dangyueaphe=0;
		//当月预付
		$dangyueapyu=0;
		//核销记录
		$aphxrecord=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
	
		->where("ahp.is_deleted=0 and ahp.state in (7)")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();
		//print_r($aphxrecord);
		for($i=0;$i<count($aphxrecord);$i++){
			$tmpap=$aphxrecord[$i];
			$Apallhexiao+=$tmpap["hanshui"];
			if(!$tmpap["fp_no"]){
				$apshengyu+=$tmpap["hanshui"];
				
			}
			if($nowt["k2start"]<=strtotime($tmpap["created_on"])&&$nowt["k2end"]+3600*24>strtotime($tmpap["created_on"])){
				$dangyueaphe+=$tmpap["hanshui"];
				if($tmpap["bu_state"]==2||!$tmpap["fp_no"]){
					$dangyueapyu+=$tmpap["hanshui"];
				}
			}

		}
		$dangyueapyu=round($dangyueapyu,2);
		$this->assign("dangyueapyu",$dangyueapyu);
		$dangyueaphe=round($dangyueaphe,2);
		$this->assign("dangyueaphe",$dangyueaphe);
		$Apallhexiao=round($Apallhexiao,2);
		$this->assign("Apallhexiao",$Apallhexiao);
		$apzhuan=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		
		->where("ahp.is_deleted=0 and ahp.state in (7)  and ahd.has_fp=1 and ahd.bu_state=2")
		->sum("ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100");
		//echo $apzhuan;
		$apyufu=$apzhuan+$apshengyu;
		$apshengyu=round($apshengyu,2);
		$apzhuan=round($apzhuan,2);
		$apyufu=round($apyufu,2);
		$this->assign("apshengyu",$apshengyu);
		$this->assign("apzhuan",$apzhuan);
		$this->assign("apyufu",$apyufu);
		//当月预付转支付
		$apdangyuezhuan=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
	
		->where("ahp.is_deleted=0 and ahp.state in (7)  and ahd.has_fp=1 and ahp.created_on>='{$startdate}' and ahp.created_on<'{$enddate}' and ahd.bu_state=2")
		->sum("ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100");
		//echo Db::table("ap_hexiao_apply")->getLastSql();
		$apdangyuezhuan=round($apdangyuezhuan,2);
		$this->assign("apdangyuezhuan",$apdangyuezhuan);

		$dangyuapyy=round($dangyueapyu-$apdangyuezhuan,2);
		$this->assign("dangyuapyy",$dangyuapyy);
	
		//Ap累积预提余额
		$apleijiyue=$Apleiji-$Apallhexiao;
		$apleijiyue=round($apleijiyue,2);
		$this->assign("apleijiyue",$apleijiyue);
		
		//当月ap支付
		$apdangzf=round(Db::table("ap_zhifu")->where("zhifu_day>='{$startdate}' and zhifu_day<'{$enddate}'")->sum("zfjin"),2);
		$this->assign("apdangzf",$apdangzf);
		//Ap所有具体支付情况
		$apallzfde=Db::table("ap_zhifu")
		->select();
		//print_r($apallzfde);
		//总ap支付
		$apallzf=0;
		
		for($i=0;$i<count($apallzfde);$i++){
			$thiszf=$apallzfde[$i];
			$apallzf+=$thiszf["zfjin"];
			
		}
		$apallzf=round($apallzf,2);

		$this->assign("apallzf",$apallzf);
		//当月ap预提剩余
		$apdangyueyts=round($Apdangyue-$dangyueaphe,2);
		$this->assign("apdangyueyts",$apdangyueyts);
		
		$this->assign("data",$data);

			//Ti核销
		$Tiallhexiao=0;$dangtihexiao=0;

		$alltihede=Db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.hanshuihexiaojine,ahp.ctime")->select();
		for($i=0;$i<count($alltihede);$i++){
			$Tiallhexiao+=$alltihede[$i]["hanshuihexiaojine"];
			if($alltihede[$i]["ctime"]>=$nowt["k2start"]&&$alltihede[$i]["ctime"]<$nowt["k2end"]+3600*24){
				$dangtihexiao+=$alltihede[$i]["hanshuihexiaojine"];
			}

			
		}

		$dangtihexiao=round($dangtihexiao,2);
		$this->assign("dangtihexiao",$dangtihexiao);
		//print_r($Tiallhexiao);exit;
		$Tiallhexiao=round($Tiallhexiao,2);
		$this->assign("Tiallhexiao",$Tiallhexiao);
		//Ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->field("bczfje,zfsj")->select();
		//累积支付
		$tiallzf=0;
		//当月支付
		$tidangyuezf=0;
		for($i=0;$i<count($tizf);$i++){
			$tiallzf+=$tizf[$i]["bczfje"];
			if($nowt["k2start"]<=strtotime($tizf[$i]["zfsj"])&&$nowt["k2end"]+3600*24>strtotime($tizf[$i]["zfsj"]))
				$tidangyuezf+=$tizf[$i]["bczfje"];
		}
		$tidangyuezf=round($tidangyuezf,2);
		$this->assign("tidangyuezf",$tidangyuezf);
		$tiallzf=round($tiallzf,2);
		$this->assign("tiallzf",$tiallzf);
		//Ti全部核销明细
		$tihexiaolist=Db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine")
		->select();
		$tiallsy=0;$tiallzhuan=0;
		for($i=0;$i<count($tihexiaolist);$i++){
			if($tihexiaolist[$i]["youwufapiao"]==0||!$tihexiaolist[$i]["fapiaohao"])
				$tiallsy+=$tihexiaolist[$i]["hanshuihexiaojine"];
			if($tihexiaolist[$i]["bu_state"]==2)
				$tiallzhuan+=$tihexiaolist[$i]["hanshuihexiaojine"];
		}
		$tiallsy=round($tiallsy,2);
		$this->assign("tiallsy",$tiallsy);
		$tiallzhuan=round($tiallzhuan,2);
		$this->assign("tiallzhuan",$tiallzhuan);
		$tially=round($tiallsy+$tiallzhuan,2);
		$this->assign("tially",$tially);
		$nowend=$nowt["k2end"]+3600*24;
		//ti当月核销明细
		$tidanglist=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9) and ahp.ctime>={$nowt["k2start"]} and ahp.ctime<{$nowend}")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine")->select();
		$tidangsy=0;$tidangzhuan=0;
		for($i=0;$i<count($tidanglist);$i++){
			if($tidanglist[$i]["youwufapiao"]==0||!$tidanglist[$i]["fapiaohao"])
				$tidangsy+=$tidanglist[$i]["hanshuihexiaojine"];
			if($tidanglist[$i]["bu_state"]==2)
				$tidangzhuan+=$tidanglist[$i]["hanshuihexiaojine"];
		}
		$tidangzhuan=round($tidangzhuan,2);
		$this->assign("tidangzhuan",$tidangzhuan);
		$tidangsy=round($tidangsy,2);
		$this->assign("tidangsy",$tidangsy);
		$tidangy=round($tidangzhuan+$tidangsy,2);
		$this->assign("tidangy",$tidangy);
		//ti累计剩余预提
		$tileijisyyt=round($Tileiji-$Tiallhexiao,2);
		$this->assign("tileijisyyt",$tileijisyyt);
		//ti当月剩余预提
		$tidangsyyt=round($Tidangyue-$dangtihexiao,2);
		$this->assign("tidangsyyt",$tidangsyyt);
		return $this->fetch();
	}
	//当月总列表导出
	public function daochualldangyue($kaohe2){
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("k2.id,k2.starttime,k1.id as k1id,k2.endtime,k2.title as yue,c.title as nian,c.id as cid,k1.starttime as k1start,k1.endtime as k1end")
			->find();
		
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$k2tianshu=($nowt["endtime"]-$nowt["starttime"])/3600/24+1;
		$k1tianshu=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		$endt=$nowt["endtime"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["starttime"]);$nowt["enddate"]=date("Y-m-d",$nowt["endtime"]+3600*24);
		//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9) and ahp.ctime<{$endt} and ahp.ctime>={$nowt["starttime"]}")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->where("zfsj>='{$nowt["startdate"]}' and zfsj<'{$nowt["enddate"]}'")->field("bczfje,zfsj")->select();
		//查询当月ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//print_r($apyt);exit;
		//查询当月ap支付
		
		$apzf=Db::table("ap_zhifu")->where("zhifu_day>='{$nowt["startdate"]}' and zhifu_day<'{$nowt["enddate"]}'")->select();
		//查询当月ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9) and ahp.created_on>='{$nowt["startdate"]}' and ahp.created_on<'{$nowt["enddate"]}'")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();

		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
					//纬度为考核期2
				if($Tiyt[$j]["erjikaoheqi"]==$nowt["id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($Tiyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]&&!$Tiyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($Tiyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			for($j=0;$j<count($apyt);$j++){
				//纬度为考核期2
				if($apyt[$j]["erjikaoheqi"]==$nowt["id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($apyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]&&!$apyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($apyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			//循环匹配当年支付
		
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			//循环匹配当年核销
			
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);

			$datas[]=$tmp;
		}
		$this->daochu($datas,$nowt["nian"].'-'.$nowt["yue"]."费用列表");
	}
	//当月总列表
	public function dangyueall($kaohe2){
		$this->assign("kaohe2",$kaohe2);
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("k2.id,k2.starttime,k1.id as k1id,k2.endtime,k2.title as yue,c.title as nian,c.id as cid,k1.starttime as k1start,k1.endtime as k1end")
			->find();
		$this->assign("nowt",$nowt);
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$k2tianshu=($nowt["endtime"]-$nowt["starttime"])/3600/24+1;
		$k1tianshu=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		$endt=$nowt["endtime"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["starttime"]);$nowt["enddate"]=date("Y-m-d",$nowt["endtime"]+3600*24);
		//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9) and ahp.ctime<{$endt} and ahp.ctime>={$nowt["starttime"]}")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->where("zfsj>='{$nowt["startdate"]}' and zfsj<'{$nowt["enddate"]}'")->field("bczfje,zfsj")->select();
		//查询当月ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//print_r($apyt);exit;
		//查询当月ap支付
		
		$apzf=Db::table("ap_zhifu")->where("zhifu_day>='{$nowt["startdate"]}' and zhifu_day<'{$nowt["enddate"]}'")->select();
		//查询当月ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9) and ahp.created_on>='{$nowt["startdate"]}' and ahp.created_on<'{$nowt["enddate"]}'")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();

		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
					//纬度为考核期2
				if($Tiyt[$j]["erjikaoheqi"]==$nowt["id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($Tiyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]&&!$Tiyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($Tiyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			for($j=0;$j<count($apyt);$j++){
				//纬度为考核期2
				if($apyt[$j]["erjikaoheqi"]==$nowt["id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($apyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]&&!$apyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($apyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			//循环匹配当年支付
		
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			//循环匹配当年核销
			
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);

			$datas[]=$tmp;
		}
		$this->assign("datas",$datas);
		return $this->fetch();
	}
	//当月ap导出
	public function daochuapdangyue($kaohe2){
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("k2.id,k2.starttime,k1.id as k1id,k2.endtime,k2.title as yue,c.title as nian,c.id as cid,k1.starttime as k1start,k1.endtime as k1end")
			->find();
		
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询当月ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//print_r($apyt);exit;
		//查询当月ap支付
		$nowt["startdate"]=date("Y-m-d",$nowt["starttime"]);$nowt["enddate"]=date("Y-m-d",$nowt["endtime"]+3600*24);
		$apzf=Db::table("ap_zhifu")->where("zhifu_day>='{$nowt["startdate"]}' and zhifu_day<'{$nowt["enddate"]}'")->select();
		//查询当月ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9) and ahp.created_on>='{$nowt["startdate"]}' and ahp.created_on<'{$nowt["enddate"]}'")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();

		$k2tianshu=($nowt["endtime"]-$nowt["starttime"])/3600/24+1;
		$k1tianshu=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		
			$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($apyt);$j++){
				//纬度为考核期2
				if($apyt[$j]["erjikaoheqi"]==$nowt["id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($apyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]&&!$apyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($apyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);



			$datas[]=$tmp;
		}
		$this->daochu($datas,$nowt["nian"].'-'.$nowt["yue"]."AP费用列表");
	}
	//当月ap
	public function dangyueap($kaohe2){
		$this->assign("kaohe2",$kaohe2);
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("k2.id,k2.starttime,k1.id as k1id,k2.endtime,k2.title as yue,c.title as nian,c.id as cid,k1.starttime as k1start,k1.endtime as k1end")
			->find();
		$this->assign("nowt",$nowt);
		$cainian=Db::table("cg_cainian")->order("starttime")->select();

		$k2tianshu=($nowt["endtime"]-$nowt["starttime"])/3600/24+1;
		$k1tianshu=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		
		$nowt["startdate"]=date("Y-m-d",$nowt["starttime"]);$nowt["enddate"]=date("Y-m-d",$nowt["endtime"]+3600*24);
		//查询当月ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//print_r($apyt);exit;
		//查询当月ap支付
		
		$apzf=Db::table("ap_zhifu")->where("zhifu_day>='{$nowt["startdate"]}' and zhifu_day<'{$nowt["enddate"]}'")->select();
		//查询当月ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9) and ahp.created_on>='{$nowt["startdate"]}' and ahp.created_on<'{$nowt["enddate"]}'")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();

			$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($apyt);$j++){
				//纬度为考核期2
				if($apyt[$j]["erjikaoheqi"]==$nowt["id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($apyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$apyt[$j]["cainian"]==$cainian[$i]["id"]&&!$apyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($apyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);



			$datas[]=$tmp;
		}
		$this->assign("datas",$datas);
		
		return $this->fetch();
	}
	//导出当月ti
	public function daochutidangyue($kaohe2){
		$this->assign("kaohe2",$kaohe2);
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("k2.id,k2.starttime,k1.id as k1id,k2.endtime,k2.title as yue,c.title as nian,c.id as cid,k1.starttime as k1start,k1.endtime as k1end")
			->find();
		
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$k2tianshu=($nowt["endtime"]-$nowt["starttime"])/3600/24+1;
		$k1tianshu=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		$endt=$nowt["endtime"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["starttime"]);$nowt["enddate"]=date("Y-m-d",$nowt["endtime"]+3600*24);
		//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9) and ahp.ctime<{$endt} and ahp.ctime>={$nowt["starttime"]}")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->where("zfsj>='{$nowt["startdate"]}' and zfsj<'{$nowt["enddate"]}'")->field("bczfje,zfsj")->select();
		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
					//纬度为考核期2
				if($Tiyt[$j]["erjikaoheqi"]==$nowt["id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($Tiyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]&&!$Tiyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($Tiyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);

			$datas[]=$tmp;
		}
		$this->daochu($datas,$nowt["nian"].'-'.$nowt["yue"]."TI费用列表");
	}
	//当月ti
	public function dangyueti($kaohe2){
		$this->assign("kaohe2",$kaohe2);
		$nowt=Db::table("cg_cainian")->alias("c")
			->join("cg_kaoheqi1 k1","k1.cid=c.id")
			->join("cg_kaoheqi2 k2","k2.pid=k1.id")
			->where("k2.id={$kaohe2}")
			->field("k2.id,k2.starttime,k1.id as k1id,k2.endtime,k2.title as yue,c.title as nian,c.id as cid,k1.starttime as k1start,k1.endtime as k1end")
			->find();
		$this->assign("nowt",$nowt);
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		$k2tianshu=($nowt["endtime"]-$nowt["starttime"])/3600/24+1;
		$k1tianshu=($nowt["k1end"]-$nowt["k1start"])/3600/24+1;
		$endt=$nowt["endtime"]+3600*24;
		$nowt["startdate"]=date("Y-m-d",$nowt["starttime"]);$nowt["enddate"]=date("Y-m-d",$nowt["endtime"]+3600*24);
		//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state in (7,9) and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2 and apd.cainian={$nowt['cid']}")
		->field("feiyong,cainian,yijikaoheqi,erjikaoheqi")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9) and ahp.ctime<{$endt} and ahp.ctime>={$nowt["starttime"]}")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->where("zfsj>='{$nowt["startdate"]}' and zfsj<'{$nowt["enddate"]}'")->field("bczfje,zfsj")->select();
		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
					//纬度为考核期2
				if($Tiyt[$j]["erjikaoheqi"]==$nowt["id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
				//纬度为考核期1时按天核算
				if($Tiyt[$j]["yijikaoheqi"]==$nowt["k1id"]&&$Tiyt[$j]["cainian"]==$cainian[$i]["id"]&&!$Tiyt[$j]["erjikaoheqi"]){
					$tmp["yuti"]+=($Tiyt[$j]["feiyong"]/$k1tianshu*$k2tianshu);
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);

			$datas[]=$tmp;
		}
		$this->assign("datas",$datas);
		return $this->fetch();

	}
	//累积全部
	public function leijiall(){
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询所有ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3")
		->field("feiyong,cainian")->select();
		//查询所有ap支付
		$apzf=Db::table("ap_zhifu")->select();
		//查询所有ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9)")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();
		
			

		
			//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2")
		->field("feiyong,cainian")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->field("bczfje,zfsj")->select();
		//按照财年组合数据
	
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
				if($Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			//循环匹配当年预提
			for($j=0;$j<count($apyt);$j++){
				if($apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

		
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);
			
			$datas[]=$tmp;
		}
		

		$this->assign("datas",$datas);
		return $this->fetch();
	}
	//导出累积
	public function daochuleijiall(){
				$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询所有ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3")
		->field("feiyong,cainian")->select();
		//查询所有ap支付
		$apzf=Db::table("ap_zhifu")->select();
		//查询所有ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9)")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();
		
			

		
			//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2")
		->field("feiyong,cainian")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->field("bczfje,zfsj")->select();
		//按照财年组合数据
	
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
				if($Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			//循环匹配当年预提
			for($j=0;$j<count($apyt);$j++){
				if($apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);
			
			$datas[]=$tmp;
		}
		
		$this->daochu($datas,"累积费用列表");
	}
	//累积ap报表
	public function leijiap(){
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询所有ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3")
		->field("feiyong,cainian")->select();
		//查询所有ap支付
		$apzf=Db::table("ap_zhifu")->select();
		//查询所有ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9)")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();
		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($apyt);$j++){
				if($apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);



			$datas[]=$tmp;
		}
		$this->assign("datas",$datas);
		return $this->fetch();
	}
	//累积ti报表
	public function leijiti(){
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2")
		->field("feiyong,cainian")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->field("bczfje,zfsj")->select();
		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
				if($Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);

			$datas[]=$tmp;
		}
		$this->assign("datas",$datas);
		return $this->fetch();
	}
	//导出累积ti
	public function daochuleijiti(){
			$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询所有ti预提
		$Tiyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=2")
		->field("feiyong,cainian")->select();
		//查询所有ti核销
		$Tihx=db::table("cg_ti_hexiao")->alias("ahp")
		->join("cg_ti_hexiao_list ahd","ahd.hx_id=ahp.id")
		->where("ahp.status in (7,9)")
		->field("ahd.bu_state,ahd.youwufapiao,ahd.fapiaohao,ahd.hanshuihexiaojine,ahp.ctime")->select();
		//查询所有ti支付
		$tizf=Db::table("cg_ti_hexiao_zhifu")->field("bczfje,zfsj")->select();
		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($Tiyt);$j++){
				if($Tiyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$Tiyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);
			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($tizf);$j++){
				$zftime=strtotime($tizf[$j]["zfsj"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$tizf[$j]["bczfje"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);
			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($Tihx);$j++){
				$hxtime=strtotime(date("Y-m-d",$Tihx[$j]["ctime"]));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["youwufapiao"]==0||!$Tihx[$j]["fapiaohao"])
						$tmp["shengyu"]+=$Tihx[$j]["hanshuihexiaojine"];
					if($Tihx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$Tihx[$j]["hanshuihexiaojine"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);

			$datas[]=$tmp;
		}
		$this->daochu($datas,"TI累积费用列表");
	}
	//导出累积ap
	public function daochuleijiap(){
		$cainian=Db::table("cg_cainian")->order("starttime")->select();
		//查询所有ap预提
		$apyt=Db::table("target_apply")->alias("ap")
		->join("target_apply_detail apd","apd.apply_id=ap.id")
		->where("ap.state=7 and ap.is_actived=1 and ap.is_deleted=0 and ap.tag<>1 and ap.type_id=3")
		->field("feiyong,cainian")->select();
		//查询所有ap支付
		$apzf=Db::table("ap_zhifu")->select();
		//查询所有ap核销
		$aphx=Db::table("ap_hexiao_apply")->alias("ahp")
		->join("ap_hexiao_detail ahd","ahd.hexiao_id=ahp.id")
		->where("ahp.is_deleted=0 and ahp.state in (7,9)")
		->field("(ahd.hexiao_weishui+ahd.hexiao_weishui*ahd.hexiao_shuilv/100) as hanshui,ahd.has_fp,ahp.created_on,ahd.bu_state,ahd.fp_no")
		->select();
		//按照财年组合数据
		$datas=[];
		for($i=0;$i<count($cainian);$i++){
			$tmp["title"]=$cainian[$i]["title"];
			$tmp["yuti"]=0;
			//循环匹配当年预提
			for($j=0;$j<count($apyt);$j++){
				if($apyt[$j]["cainian"]==$cainian[$i]["id"]){
					$tmp["yuti"]+=$apyt[$j]["feiyong"];
				}
			}
			$tmp["yuti"]=round($tmp["yuti"],2);

			//循环匹配当年支付
			$tmp["zhifu"]=0;
			for($j=0;$j<count($apzf);$j++){
				$zftime=strtotime($apzf[$j]["zhifu_day"]);
				if($zftime>=$cainian[$i]["starttime"]&&$zftime<=$cainian[$i]["endtime"]){
					$tmp["zhifu"]+=$apzf[$j]["zfjin"];
				}
			}
			$tmp["zhifu"]=round($tmp["zhifu"],2);

			//循环匹配当年核销
			$tmp["hexiao"]=0;$tmp["shengyu"]=0;$tmp["zhuan"]=0;
			for($j=0;$j<count($aphx);$j++){
				$hxtime=strtotime(substr($aphx[$j]["created_on"], 0,10));
				if($hxtime>=$cainian[$i]["starttime"]&&$hxtime<=$cainian[$i]["endtime"]){
					$tmp["hexiao"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["has_fp"]==0||!$aphx[$j]["fp_no"])
						$tmp["shengyu"]+=$aphx[$j]["hanshui"];
					if($aphx[$j]["bu_state"]==2)
						$tmp["zhuan"]+=$aphx[$j]["hanshui"];
				}
			}
			$tmp["zongyu"]=round($tmp["shengyu"]+$tmp["zhuan"],2);$tmp["ytye"]=round($tmp["yuti"]-$tmp["hexiao"],2);
			$tmp["hexiao"]=round($tmp["hexiao"],2);$tmp["shengyu"]=round($tmp["shengyu"],2);$tmp["zhuan"]=round($tmp["zhuan"],2);



			$datas[]=$tmp;
		}
	$this->daochu($datas,"AP累积费用列表");
	}
	//导出ap预提费用报表
	public function daochutipre($start,$end){
			$lie=[];
			for($i=-1;$i<26;$i++){
				if($i>-1){
					$tmp1=chr($i+97);
				}else
					$tmp1="";
				for($j=0;$j<26;$j++){
					$lie[]=$tmp1.chr($j+97);
				}
			}
			//print_r($lie);
			$where="t.is_deleted=0 and t.is_actived=1 and t.state=7 and t.tag<>1 and t.type_id=2 ";
				//$data=["kaohe1_start"];
			//获得当前财年和当前考核期
		$data["kaohe_start"]=$start;$data["kaohe_end"]=$end;
		$stime=Db::table("cg_kaoheqi1")->where(["id"=>$start])->value("starttime");
		$etime=Db::table("cg_kaoheqi1")->where(["id"=>$end])->value("endtime");
		$where.="and k1.starttime>=$stime and k1.endtime<=$etime";

		if($data["kaohe_end"]==$data["kaohe_start"]){
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}";
		}else{
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$strarr1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_end"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}至{$strarr1["cainian"]}-{$strarr1["kaohe"]}";
		}

		$channels=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
		->where($where)
		->group("ap.channel1,c1.id,c1.title")->field("c1.id,c1.title")
		->select();
		//print_r($channels);exit;
		$channels_pro=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
		->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
		->where($where)
		->group("ap.channel1,c1.id,c1.title,ap.pinpai,pb.id,pb.brand_name")->field("c1.id as cid,c1.title as cainian,pb.id as bid,pb.brand_name")
		->select();
		
		$allcounts=0;
		for($i=0;$i<count($channels);$i++){
			$pros=[];$counts=1;$proids=[];
			for($j=0;$j<count($channels_pro);$j++){
				if($channels[$i]["id"]==$channels_pro[$j]["cid"]){
					$pros[]=$channels_pro[$j]["brand_name"];
					$proids[]=$channels_pro[$j]["bid"];
					$counts++;
					
				}
			}
			$channels[$i]["pros"]=$pros;$channels[$i]["counts"]=$counts;$channels[$i]["proids"]=$proids;
			$allcounts+=$counts;
		}


		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("Ti费用预提报表");
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

        if(!$allcounts){
        	//没有数据
        	    


          $objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$str}Ti费用 By Region City Brand Channel Report");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
        #标题
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
         $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('0099CCFF');
         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

         $objPHPExcel->getActiveSheet()->setCellValue('A3',"分区");
         $objPHPExcel->getActiveSheet()->setCellValue('B3',"城市");
         $objPHPExcel->getActiveSheet()->setCellValue('C3',"{$str}Ti费用对账明细");
         $objPHPExcel->getActiveSheet()->setCellValue('D3',"Total");


         //结尾
           $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
         $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getFill()->getStartColor()->setARGB('00FFCC99');
         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->setCellValue('A4',"Total汇总");
          $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
         $objPHPExcel->getActiveSheet()->setCellValue('C4',"0");
         $objPHPExcel->getActiveSheet()->setCellValue('D4',"0");
           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
 		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:D4')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        }else{
        	//有数据
        	    


	          $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$lie[2+$allcounts].'1');
	        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$str}Ti费用 By Region City Brand Channel Report");
	              //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //加粗
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

	            #################表头信息
	        $objPHPExcel->getActiveSheet()->mergeCells('A2:'.$lie[2+$allcounts].'2');
	        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
	                //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //加粗
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
	          $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
	           $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	            $objPHPExcel->getActiveSheet()->getStyle('5')->getAlignment()->setWrapText(true);
	         $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	          

	         $objPHPExcel->getActiveSheet()->setCellValue('A3',"分区");
	          $objPHPExcel->getActiveSheet()->mergeCells('A3:A5');
         	$objPHPExcel->getActiveSheet()->setCellValue('B3',"城市");
         	$objPHPExcel->getActiveSheet()->mergeCells('B3:B5');
        	 $objPHPExcel->getActiveSheet()->setCellValue('C3',"{$str}Ti费用对账明细");
        	  $objPHPExcel->getActiveSheet()->mergeCells('C3:'.$lie[1+$allcounts].'3');
        	$objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].'3',"Total");
        	$objPHPExcel->getActiveSheet()->mergeCells($lie[2+$allcounts].'3:'.$lie[2+$allcounts].'5');
        	$lims=2;$lime=2;
        	for($i=0;$i<count($channels);$i++){
        		$objPHPExcel->getActiveSheet()->setCellValue($lie[$lims].'4',$channels[$i]["title"]);
        		$lime=$lims+$channels[$i]["counts"]-1;
	          	$objPHPExcel->getActiveSheet()->mergeCells($lie[$lims].'4:'.$lie[$lime].'4');

	          	for($j=0;$j<count($channels[$i]["pros"]);$j++){
	          		$objPHPExcel->getActiveSheet()->setCellValue($lie[$lims+$j].'5',$channels[$i]["pros"][$j]);
	          	}
	          	$objPHPExcel->getActiveSheet()->setCellValue($lie[$lime].'5',"Total");
	          	$lims=$lime+1;


        	}


		//数据
		$list=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
		->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
		->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
		->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
		->where($where)->group("z.id,c.id,ap.channel1,c1.id,c1.title,ap.pinpai,pb.id,pb.brand_name")
		->field("z.title as fenqu,z.id as fenquid,c.title as city,c.id as shiid,c1.title as channel1,c1.id as channelid,pb.brand_name,pb.id as bid,sum(ap.feiyong) as feiyonge")
		->select();
		
		//print_r($list);
		$fenqu=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")->where($where)->group("z.id")->field("z.id,z.title,sum(ap.feiyong) as zonge")->select();

		for($i=0;$i<count($fenqu);$i++){
			$feiyong=[];$jishu=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyong[$jishu]=0;
					for($l=0;$l<count($list);$l++){
						//分区相同，渠道相同，品牌相同
						if(($fenqu[$i]["id"]==$list[$l]["fenquid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyong[$jishu]+=$list[$l]["feiyonge"];
							$flag=1;
							
						}
					}
					if($flag==0){
						$feiyong[$jishu]=0;
					}
					$jishu++;
				}
				$feiyong[$jishu]=$totle;
				$jishu++;
			}
			$fenqu[$i]["feiyong"]=$feiyong;
		}
		
		$shi=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
		->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
		->where($where)->group("z.id,c.id")->field("z.id as zid,c.id,c.title as city,z.title as fenqu,sum(ap.feiyong) as zonge")->select();


		for($i=0;$i<count($shi);$i++){
			$feiyongi=[];
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;
					for($l=0;$l<count($list);$l++){
						//分区相同,市相同，渠道相同，品牌相同
						if(($shi[$i]["zid"]==$list[$l]["fenquid"])&&($shi[$i]["id"]==$list[$l]["shiid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongi[]=$list[$l]["feiyonge"];
							$flag=1;
							break;
						}
					}
					if($flag==0){
						$feiyongi[]=0;
					}
				}
				$feiyongi[]=$totle;
			}
			$shi[$i]["feiyong"]=$feiyongi;
		}
//print_r($shi);exit;
	
		$feiyongt=[];$allfeiyong=0;$js=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyongt[$js]=0;
					for($l=0;$l<count($list);$l++){
						//渠道相同，品牌相同
						if(($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongt[$js]+=$list[$l]["feiyonge"];
							$flag=1;$allfeiyong+=$list[$l]["feiyonge"];
							
						}
					}
					if($flag==0){
						$feiyongt[$js]=0;
					}
					$js++;
				}
				$feiyongt[$js]=$totle;$js++;
			}

			$h=6;
			for($i=0;$i<count($fenqu);$i++){
				//分区
				for($j=0;$j<count($shi);$j++){
					if($fenqu[$i]["id"]==$shi[$j]["zid"]){
						//市
						$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				        $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$shi[$j]["fenqu"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$shi[$j]["city"]);
				        $objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].$h,$shi[$j]["zonge"]);
				        $s=1;
				        for($l=0;$l<count($shi[$j]["feiyong"]);$l++){
				        	$s++;
				        	 $objPHPExcel->getActiveSheet()->setCellValue($lie[$s].$h,$shi[$j]["feiyong"][$l]);
				        }

				        $h++;
					}
					
				}
					$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
					 $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        		$objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->getStartColor()->setARGB('00FFCC99');
	        		 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$fenqu[$i]["title"]."汇总");
	        		 $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:B{$h}");
	        		 $sf=1;
	        		 for($l=0;$l<count($fenqu[$i]["feiyong"]);$l++){
	        		 	$sf++;
	        		 	 $objPHPExcel->getActiveSheet()->setCellValue($lie[$sf].$h,$fenqu[$i]["feiyong"][$l]);
	        		 }
				     $objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].$h,$fenqu[$i]["zonge"]);
				     $h++;
			}
			$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
					 $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        		$objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->getStartColor()->setARGB('00FFCC99');
			 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"Total汇总");
	        $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:B{$h}");
	        $st=1;
	        for($i=0;$i<count($feiyongt);$i++){
	        	$st++;
	        		 	 $objPHPExcel->getActiveSheet()->setCellValue($lie[$st].$h,$feiyongt[$i]);
	        }
	         $objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].$h,$allfeiyong);



	        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:'.$lie[2+$allcounts].$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        }


















        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=Ti费用预提报表.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//ti预提费用报表
	public function tipre(){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->select();
		$where="t.is_deleted=0 and t.is_actived=1 and t.state=7 and t.type_id=2 and t.tag<>1 ";
		if($data){

		}else{
			//$data=["kaohe1_start"];
			//获得当前财年和当前考核期
			$nowtime=time();
			for($i=0;$i<count($cainian);$i++){
				if($nowtime<=$cainian[$i]["endtime"]&&$nowtime>=$cainian[$i]["starttime"]){
					$data["cainian_start"]=$cainian[$i]["id"];break;
				}
			}
			$data["cainian_end"]=$data["cainian_start"];
			for($i=0;$i<count($kaohe1);$i++){
				if($kaohe1[$i]["cid"]==$data["cainian_start"]){
					$data["kaohe_start"]=$kaohe1[$i]["id"];
					if($nowtime>=$kaohe1[$i]["starttime"]&&$nowtime<=$kaohe1[$i]["endtime"]){
						$data["kaohe_start"]=$kaohe1[$i]["id"];
						break;
					}
				}
			}
			$data["kaohe_end"]=$data["kaohe_start"];

		}
		$stime=Db::table("cg_kaoheqi1")->where(["id"=>$data["kaohe_start"]])->value("starttime");
		$etime=Db::table("cg_kaoheqi1")->where(["id"=>$data["kaohe_end"]])->value("endtime");
		$etime+=(3600*24);
		$where.="and k1.starttime>=$stime and k1.endtime<=$etime";

		if($data["kaohe_end"]==$data["kaohe_start"]){
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}销量费用对账明细表";
		}else{
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$strarr1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_end"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}至{$strarr1["cainian"]}-{$strarr1["kaohe"]}销量费用对账明细表";
		}
		$this->assign("str",$str);
		$this->assign("data",$data);
		//print_r($data);
		$this->assign("kaohe1",$kaohe1);$this->assign("cainian",$cainian);

		$channels=Db::table("cg_ti")->alias("ap")
		->join("cg_jxs j","ap.jxs_id=j.id")
		->join("cg_channel_types1 c1","j.channeltype=c1.id","LEFT")
		->where("ap.status=7 and (ap.status2 is null or ap.status2=0) and ap.ctime>=$stime and ap.ctime<$etime")
		->group("j.channeltype,c1.id,c1.title")->field("c1.id,c1.title")
		->select();
		//print_r($channels);exit;
		$channels_pro=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
		->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
		->where($where)
		->group("ap.channel1,c1.id,c1.title,ap.pinpai,pb.id,pb.brand_name")->field("c1.id as cid,c1.title as cainian,pb.id as bid,pb.brand_name")
		->select();
		
		$allcounts=0;
		for($i=0;$i<count($channels);$i++){
			$pros=[];$counts=1;$proids=[];
			for($j=0;$j<count($channels_pro);$j++){
				if($channels[$i]["id"]==$channels_pro[$j]["cid"]){
					$pros[]=$channels_pro[$j]["brand_name"];
					$proids[]=$channels_pro[$j]["bid"];
					$counts++;
					
				}
			}
			$channels[$i]["pros"]=$pros;$channels[$i]["counts"]=$counts;$channels[$i]["proids"]=$proids;
			$allcounts+=$counts;
		}
		//print_r($channels);exit;
		$this->assign("allcounts",$allcounts);$this->assign("channels",$channels);
		//费用列表
	


		//数据
		$list=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
		->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
		->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
		->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
		->where($where)->group("z.id,c.id,ap.channel1,c1.id,c1.title,ap.pinpai,pb.id,pb.brand_name")
		->field("z.title as fenqu,z.id as fenquid,c.title as city,c.id as shiid,c1.title as channel1,c1.id as channelid,pb.brand_name,pb.id as bid,sum(ap.feiyong) as feiyonge")
		->select();
		
		//print_r($list);
		$fenqu=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")->where($where)->group("z.id")->field("z.id,z.title,sum(ap.feiyong) as zonge")->select();

		for($i=0;$i<count($fenqu);$i++){
			$feiyong=[];$jishu=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyong[$jishu]=0;
					for($l=0;$l<count($list);$l++){
						//分区相同，渠道相同，品牌相同
						if(($fenqu[$i]["id"]==$list[$l]["fenquid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyong[$jishu]+=$list[$l]["feiyonge"];
							$flag=1;
							
						}
					}
					if($flag==0){
						$feiyong[$jishu]=0;
					}
					$jishu++;
				}
				$feiyong[$jishu]=$totle;
				$jishu++;
			}
			$fenqu[$i]["feiyong"]=$feiyong;
		}
		$this->assign("fenqu",$fenqu);

		$shi=Db::table("target_apply")->alias("t")
		->join("target_apply_detail ap","t.id=ap.apply_id")
		->join("cg_cainian cn","ap.cainian=cn.id")
		->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id")
		->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
		->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
		->where($where)->group("z.id,c.id")->field("z.id as zid,c.id,c.title as city,z.title as fenqu,sum(ap.feiyong) as zonge")->select();


		for($i=0;$i<count($shi);$i++){
			$feiyongi=[];
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;
					for($l=0;$l<count($list);$l++){
						//分区相同,市相同，渠道相同，品牌相同
						if(($shi[$i]["zid"]==$list[$l]["fenquid"])&&($shi[$i]["id"]==$list[$l]["shiid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongi[]=$list[$l]["feiyonge"];
							$flag=1;
							break;
						}
					}
					if($flag==0){
						$feiyongi[]=0;
					}
				}
				$feiyongi[]=$totle;
			}
			$shi[$i]["feiyong"]=$feiyongi;
		}
//print_r($shi);exit;
		//
		$this->assign("shi",$shi);

		$feiyongt=[];$allfeiyong=0;$js=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyongt[$js]=0;
					for($l=0;$l<count($list);$l++){
						//渠道相同，品牌相同
						if(($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongt[$js]+=$list[$l]["feiyonge"];
							$flag=1;$allfeiyong+=$list[$l]["feiyonge"];
							
						}
					}
					if($flag==0){
						$feiyongt[$js]=0;
					}
					$js++;
				}
				$feiyongt[$js]=$totle;$js++;
			}
		$this->assign("feiyongt",$feiyongt);$this->assign("allfeiyong",$allfeiyong);
		return $this->fetch();

		return $this->fetch();
	}
	//ap预提费用报表
	public function appre(){
		$request = Request::instance();$data=$request->post();
		$cainian=Db::table("cg_cainian")->select();
		$kaohe1=Db::table("cg_kaoheqi1")->select();
		$where="t.is_deleted=0 and t.is_actived=1 and t.state=7 and t.type_id=3 and t.tag<>1 ";
		if($data){

		}else{
			//$data=["kaohe1_start"];
			//获得当前财年和当前考核期
			$nowtime=time();
			for($i=0;$i<count($cainian);$i++){
				if($nowtime<=$cainian[$i]["endtime"]&&$nowtime>=$cainian[$i]["starttime"]){
					$data["cainian_start"]=$cainian[$i]["id"];break;
				}
			}
			$data["cainian_end"]=$data["cainian_start"];
			for($i=0;$i<count($kaohe1);$i++){
				if($kaohe1[$i]["cid"]==$data["cainian_start"]){
					$data["kaohe_start"]=$kaohe1[$i]["id"];
					if($nowtime>=$kaohe1[$i]["starttime"]&&$nowtime<=$kaohe1[$i]["endtime"]){
						$data["kaohe_start"]=$kaohe1[$i]["id"];
						break;
					}
				}
			}
			$data["kaohe_end"]=$data["kaohe_start"];

		}
		$stime=Db::table("cg_kaoheqi1")->where(["id"=>$data["kaohe_start"]])->value("starttime");
		$etime=Db::table("cg_kaoheqi1")->where(["id"=>$data["kaohe_end"]])->value("endtime");
		$where.="and k1.starttime>=$stime and k1.endtime<=$etime";

		if($data["kaohe_end"]==$data["kaohe_start"]){
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}销量费用对账明细表";
		}else{
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$strarr1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_end"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}至{$strarr1["cainian"]}-{$strarr1["kaohe"]}销量费用对账明细表";
		}
		$this->assign("str",$str);
		$this->assign("data",$data);
		//print_r($data);
		$this->assign("kaohe1",$kaohe1);$this->assign("cainian",$cainian);
		$sdate=date("Y-m-d H:i:s",$stime);
		$edate=date("Y-m-d",$etime);
		$edate.=" 23:59:59";
		$channels=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("ap.channel_id")
		->group("ap.channel_id,c1.id,c1.title")->field("c1.id,c1.title")
		->select();
		//print_r($channels);exit;
		$channels_pro=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")
		->join("pro_brands pb","apd.brand=pb.id","LEFT")
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")
		->group("ap.channel_id,c1.id,c1.title,apd.brand,pb.id,pb.brand_name")->field("c1.id as cid,c1.title as cainian,pb.id as bid,pb.brand_name")
		->select();
		
		$allcounts=0;
		for($i=0;$i<count($channels);$i++){
			$pros=[];$counts=1;$proids=[];
			for($j=0;$j<count($channels_pro);$j++){
				if($channels[$i]["id"]==$channels_pro[$j]["cid"]){
					$pros[]=$channels_pro[$j]["brand_name"];
					$proids[]=$channels_pro[$j]["bid"];
					$counts++;
					
				}
			}
			$channels[$i]["pros"]=$pros;$channels[$i]["counts"]=$counts;$channels[$i]["proids"]=$proids;
			$allcounts+=$counts;
		}
		//print_r($channels);exit;
		$this->assign("allcounts",$allcounts);$this->assign("channels",$channels);
		//费用列表
	


		//数据
		$list=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")
		->join("pro_brands pb","apd.brand=pb.id","LEFT")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","apd.sheng=p.id","LEFT")
        ->join("cg_market_city c","apd.shi=c.id","LEFT")
		
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("z.id,c.id,ap.channel_id,c1.id,c1.title,apd.brand,pb.id,pb.brand_name")
		->field("z.title as fenqu,z.id as fenquid,c.title as city,c.id as shiid,c1.title as channel1,c1.id as channelid,pb.brand_name,pb.id as bid,sum(apd.pifuzonge*(1+apd.shuilv/100)) as feiyonge")
		->select();
		
		//print_r($list);
		$fenqu=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("z.id")->field("z.id,z.title,sum(apd.pifuzonge*(1+apd.shuilv/100)) as zonge")->select();

		for($i=0;$i<count($fenqu);$i++){
			$feiyong=[];$jishu=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyong[$jishu]=0;
					for($l=0;$l<count($list);$l++){
						//分区相同，渠道相同，品牌相同
						if(($fenqu[$i]["id"]==$list[$l]["fenquid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyong[$jishu]+=$list[$l]["feiyonge"];
							$flag=1;
							
						}
					}
					if($flag==0){
						$feiyong[$jishu]=0;
					}
					$jishu++;
				}
				$feiyong[$jishu]=$totle;
				$jishu++;
			}
			$fenqu[$i]["feiyong"]=$feiyong;
		}
		$this->assign("fenqu",$fenqu);

		$shi=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
		->join("cg_market_provice p","apd.sheng=p.id","LEFT")
        ->join("cg_market_city c","apd.shi=c.id","LEFT")
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("z.id,c.id")->field("z.id as zid,c.id,c.title as city,z.title as fenqu,sum(apd.pifuzonge*(1+apd.shuilv/100)) as zonge")->select();


		for($i=0;$i<count($shi);$i++){
			$feiyongi=[];
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;
					for($l=0;$l<count($list);$l++){
						//分区相同,市相同，渠道相同，品牌相同
						if(($shi[$i]["zid"]==$list[$l]["fenquid"])&&($shi[$i]["id"]==$list[$l]["shiid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongi[]=$list[$l]["feiyonge"];
							$flag=1;
							break;
						}
					}
					if($flag==0){
						$feiyongi[]=0;
					}
				}
				$feiyongi[]=$totle;
			}
			$shi[$i]["feiyong"]=$feiyongi;
		}
//print_r($shi);exit;
		//
		$this->assign("shi",$shi);

		$feiyongt=[];$allfeiyong=0;$js=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyongt[$js]=0;
					for($l=0;$l<count($list);$l++){
						//渠道相同，品牌相同
						if(($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongt[$js]+=$list[$l]["feiyonge"];
							$flag=1;$allfeiyong+=$list[$l]["feiyonge"];
							
						}
					}
					if($flag==0){
						$feiyongt[$js]=0;
					}
					$js++;
				}
				$feiyongt[$js]=$totle;$js++;
			}
		$this->assign("feiyongt",$feiyongt);$this->assign("allfeiyong",$allfeiyong);
		return $this->fetch();
	}

	//导出ap预提费用报表
	public function daochuappre($start,$end){
			$lie=[];
			for($i=-1;$i<26;$i++){
				if($i>-1){
					$tmp1=chr($i+97);
				}else
					$tmp1="";
				for($j=0;$j<26;$j++){
					$lie[]=$tmp1.chr($j+97);
				}
			}
			//print_r($lie);
			$where="t.is_deleted=0 and t.is_actived=1 and t.state=7 and t.tag<>1 and t.type_id=3 ";
				//$data=["kaohe1_start"];
			//获得当前财年和当前考核期
		$data["kaohe_start"]=$start;$data["kaohe_end"]=$end;
		$stime=Db::table("cg_kaoheqi1")->where(["id"=>$start])->value("starttime");
		$etime=Db::table("cg_kaoheqi1")->where(["id"=>$end])->value("endtime");
		$where.="and k1.starttime>=$stime and k1.endtime<=$etime";

		if($data["kaohe_end"]==$data["kaohe_start"]){
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}";
		}else{
			$strarr=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_start"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$strarr1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")->where(["k1.id"=>$data["kaohe_end"]])->field("c.title as cainian,k1.title as kaohe")->find();
			$str="{$strarr["cainian"]}-{$strarr["kaohe"]}至{$strarr1["cainian"]}-{$strarr1["kaohe"]}";
		}

		$sdate=date("Y-m-d H:i:s",$stime);
		$edate=date("Y-m-d",$etime);
		$edate.=" 23:59:59";
		$channels=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("ap.channel_id")
		->group("ap.channel_id,c1.id,c1.title")->field("c1.id,c1.title")
		->select();
		//print_r($channels);exit;
		$channels_pro=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")
		->join("pro_brands pb","apd.brand=pb.id","LEFT")
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")
		->group("ap.channel_id,c1.id,c1.title,apd.brand,pb.id,pb.brand_name")->field("c1.id as cid,c1.title as cainian,pb.id as bid,pb.brand_name")
		->select();
		$allcounts=0;
		for($i=0;$i<count($channels);$i++){
			$pros=[];$counts=1;$proids=[];
			for($j=0;$j<count($channels_pro);$j++){
				if($channels[$i]["id"]==$channels_pro[$j]["cid"]){
					$pros[]=$channels_pro[$j]["brand_name"];
					$proids[]=$channels_pro[$j]["bid"];
					$counts++;
					
				}
			}
			$channels[$i]["pros"]=$pros;$channels[$i]["counts"]=$counts;$channels[$i]["proids"]=$proids;
			$allcounts+=$counts;
		}


		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("Ap费用预提报表");
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

        if(!$allcounts){
        	//没有数据
        	    


          $objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$str}AP费用 By Region City Brand Channel Report");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
        #标题
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
         $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('0099CCFF');
         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

         $objPHPExcel->getActiveSheet()->setCellValue('A3',"分区");
         $objPHPExcel->getActiveSheet()->setCellValue('B3',"城市");
         $objPHPExcel->getActiveSheet()->setCellValue('C3',"{$str}AP费用对账明细");
         $objPHPExcel->getActiveSheet()->setCellValue('D3',"Total");


         //结尾
           $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
         $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getFill()->getStartColor()->setARGB('00FFCC99');
         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A4:D4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->setCellValue('A4',"Total汇总");
          $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
         $objPHPExcel->getActiveSheet()->setCellValue('C4',"0");
         $objPHPExcel->getActiveSheet()->setCellValue('D4',"0");
           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
 		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:D4')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        }else{
        	//有数据
        	    


	          $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$lie[2+$allcounts].'1');
	        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$str}AP费用 By Region City Brand Channel Report");
	              //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //加粗
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

	            #################表头信息
	        $objPHPExcel->getActiveSheet()->mergeCells('A2:'.$lie[2+$allcounts].'2');
	        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
	                //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //加粗
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
	          $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
	           $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
	            $objPHPExcel->getActiveSheet()->getStyle('5')->getAlignment()->setWrapText(true);
	         $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$lie[2+$allcounts].'5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	          

	         $objPHPExcel->getActiveSheet()->setCellValue('A3',"分区");
	          $objPHPExcel->getActiveSheet()->mergeCells('A3:A5');
         	$objPHPExcel->getActiveSheet()->setCellValue('B3',"城市");
         	$objPHPExcel->getActiveSheet()->mergeCells('B3:B5');
        	 $objPHPExcel->getActiveSheet()->setCellValue('C3',"{$str}AP费用对账明细");
        	  $objPHPExcel->getActiveSheet()->mergeCells('C3:'.$lie[1+$allcounts].'3');
        	$objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].'3',"Total");
        	$objPHPExcel->getActiveSheet()->mergeCells($lie[2+$allcounts].'3:'.$lie[2+$allcounts].'5');
        	$lims=2;$lime=2;
        	for($i=0;$i<count($channels);$i++){
        		$objPHPExcel->getActiveSheet()->setCellValue($lie[$lims].'4',$channels[$i]["title"]);
        		$lime=$lims+$channels[$i]["counts"]-1;
	          	$objPHPExcel->getActiveSheet()->mergeCells($lie[$lims].'4:'.$lie[$lime].'4');

	          	for($j=0;$j<count($channels[$i]["pros"]);$j++){
	          		$objPHPExcel->getActiveSheet()->setCellValue($lie[$lims+$j].'5',$channels[$i]["pros"][$j]);
	          	}
	          	$objPHPExcel->getActiveSheet()->setCellValue($lie[$lime].'5',"Total");
	          	$lims=$lime+1;


        	}


		//数据
		$list=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")
		->join("pro_brands pb","apd.brand=pb.id","LEFT")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","apd.sheng=p.id","LEFT")
        ->join("cg_market_city c","apd.shi=c.id","LEFT")
		
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("z.id,c.id,ap.channel_id,c1.id,c1.title,apd.brand,pb.id,pb.brand_name")
		->field("z.title as fenqu,z.id as fenquid,c.title as city,c.id as shiid,c1.title as channel1,c1.id as channelid,pb.brand_name,pb.id as bid,sum(apd.pifuzonge*(1+apd.shuilv/100)) as feiyonge")
		->select();
		
		//print_r($list);
		$fenqu=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("z.id")->field("z.id,z.title,sum(apd.pifuzonge*(1+apd.shuilv/100)) as zonge")->select();

		for($i=0;$i<count($fenqu);$i++){
			$feiyong=[];$jishu=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyong[$jishu]=0;
					for($l=0;$l<count($list);$l++){
						//分区相同，渠道相同，品牌相同
						if(($fenqu[$i]["id"]==$list[$l]["fenquid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyong[$jishu]+=$list[$l]["feiyonge"];
							$flag=1;
							
						}
					}
					if($flag==0){
						$feiyong[$jishu]=0;
					}
					$jishu++;
				}
				$feiyong[$jishu]=$totle;
				$jishu++;
			}
			$fenqu[$i]["feiyong"]=$feiyong;
		}
		
		$shi=Db::table("ap_apply")->alias("ap")
		->join("cg_channel_types1 c1","ap.channel_id=c1.id","LEFT")
		->join("ap_apply_detail apd","apd.apply_id=ap.id")->join("cg_market_zoning z","apd.fenqu=z.id","LEFT")
		->join("cg_market_provice p","apd.sheng=p.id","LEFT")
        ->join("cg_market_city c","apd.shi=c.id","LEFT")
		->where("ap.state=7 and ap.tag=0 and ap.is_deleted=0 and ap.is_actived=1 and ap.is_shengxiao=1 and ap.created_on>='$sdate' and ap.created_on<='$edate'")->group("z.id,c.id")->field("z.id as zid,c.id,c.title as city,z.title as fenqu,sum(apd.pifuzonge*(1+apd.shuilv/100)) as zonge")->select();


		for($i=0;$i<count($shi);$i++){
			$feiyongi=[];
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;
					for($l=0;$l<count($list);$l++){
						//分区相同,市相同，渠道相同，品牌相同
						if(($shi[$i]["zid"]==$list[$l]["fenquid"])&&($shi[$i]["id"]==$list[$l]["shiid"])&&($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongi[]=$list[$l]["feiyonge"];
							$flag=1;
							break;
						}
					}
					if($flag==0){
						$feiyongi[]=0;
					}
				}
				$feiyongi[]=$totle;
			}
			$shi[$i]["feiyong"]=$feiyongi;
		}
//print_r($shi);exit;
	
		$feiyongt=[];$allfeiyong=0;$js=0;
			for($j=0;$j<count($channels);$j++){
				$totle=0;
				for($k=0;$k<count($channels[$j]["pros"]);$k++){
					$flag=0;$feiyongt[$js]=0;
					for($l=0;$l<count($list);$l++){
						//渠道相同，品牌相同
						if(($channels[$j]["id"]==$list[$l]["channelid"])&&($channels[$j]["pros"][$k]==$list[$l]["brand_name"])){
							$totle+=$list[$l]["feiyonge"];
							$feiyongt[$js]+=$list[$l]["feiyonge"];
							$flag=1;$allfeiyong+=$list[$l]["feiyonge"];
							
						}
					}
					if($flag==0){
						$feiyongt[$js]=0;
					}
					$js++;
				}
				$feiyongt[$js]=$totle;$js++;
			}

			$h=6;
			for($i=0;$i<count($fenqu);$i++){
				//分区
				for($j=0;$j<count($shi);$j++){
					if($fenqu[$i]["id"]==$shi[$j]["zid"]){
						//市
						$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				        $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$shi[$j]["fenqu"]);
				        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$shi[$j]["city"]);
				        $objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].$h,$shi[$j]["zonge"]);
				        $s=1;
				        for($l=0;$l<count($shi[$j]["feiyong"]);$l++){
				        	$s++;
				        	 $objPHPExcel->getActiveSheet()->setCellValue($lie[$s].$h,$shi[$j]["feiyong"][$l]);
				        }

				        $h++;
					}
					
				}
					$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
					 $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        		$objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->getStartColor()->setARGB('00FFCC99');
	        		 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$fenqu[$i]["title"]."汇总");
	        		 $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:B{$h}");
	        		 $sf=1;
	        		 for($l=0;$l<count($fenqu[$i]["feiyong"]);$l++){
	        		 	$sf++;
	        		 	 $objPHPExcel->getActiveSheet()->setCellValue($lie[$sf].$h,$fenqu[$i]["feiyong"][$l]);
	        		 }
				     $objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].$h,$fenqu[$i]["zonge"]);
				     $h++;
			}
			$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
								  //水平剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				        //垂直剧中
				    $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
					 $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	        		$objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$lie[2+$allcounts].$h)->getFill()->getStartColor()->setARGB('00FFCC99');
			 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"Total汇总");
	        $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:B{$h}");
	        $st=1;
	        for($i=0;$i<count($feiyongt);$i++){
	        	$st++;
	        		 	 $objPHPExcel->getActiveSheet()->setCellValue($lie[$st].$h,$feiyongt[$i]);
	        }
	         $objPHPExcel->getActiveSheet()->setCellValue($lie[2+$allcounts].$h,$allfeiyong);



	        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:'.$lie[2+$allcounts].$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        }


















        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=Ap费用预提报表.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}


	//支付明细列表
	public function zhifu_detail($page=1,$list=10){
		$request = Request::instance();$data=$request->post();
		$pay=Db::table("cg_paymethod")->select();
		$this->assign("pay",$pay);
		$where="1=1 ";
		if($data){
			if(trim($data["jxs_no"])){
				$jxs_no=trim($data["jxs_no"]);
				$where.="and j.sn='{$jxs_no}' ";
			}
			if(trim($data["jxs_name"])){
				$jxs_name=trim($data["jxs_name"]);
				$where.="and j.title='{$jxs_name}' ";
			}
			if(trim($data["gys_no"])){
				$gys_no=trim($data["gys_no"]);
				$where.="and g.no='{$gys_no}' ";
			}
			if(trim($data["gys_name"])){
				$gys_name=trim($data["gys_name"]);
				$where.="and g.gys_name='{$gys_name}' ";
			}
			if(trim($data["he_no"])){
				$tmp=explode('_', trim($data["he_no"]));
				if(count($tmp)==1){
					$where.="and l.he_no='{$tmp[0]}' ";
				}else if(count($tmp)==2){
					$where.="and l.he_no='{$tmp[0]}' and l.he_de_no='{$tmp[1]}' ";
				}
			}
			if(trim($data["ap_no"])){
				$tmp=explode('_', trim($data["ap_no"]));
				if(count($tmp)==1){
					$where.="and l.ap_no='{$tmp[0]}' ";
				}else if(count($tmp)==2){
					$where.="and l.ap_no='{$tmp[0]}' and l.ap_de_no='{$tmp[1]}' ";
				}
			}
			if($data["pay"]){
				$where.="and l.zfid={$data["pay"]} ";
			}
			if($data["type"]){
				$where.="and l.type={$data["type"]} ";
			}
			if(trim($data["pinzheng"])){
				$pinzheng=trim($data["pinzheng"]);
				$where.="and l.pinzheng='{$pinzheng}' ";
			}
			if($data["start"]){
				$where.="and l.zfday>='{$data["start"]}' ";
			}
			if($data["end"]){
				$where.="and l.zfday<='{$data["end"]}' ";
			}
		}else{
			$data=["jxs_no"=>"","jxs_name"=>"","gys_no"=>"","gys_name"=>"","he_no"=>"","ap_no"=>"","pay"=>"","type"=>0,"pinzheng"=>"","start"=>"","end"=>""];
		}
		$this->assign("data",$data);
		$sum=Db::table("apti_zhifu_list")->alias("l")
		->join("cg_jxs j","l.jxs=j.id","LEFT")
		->join("gys g","l.gys=g.id","LEFT")
		->join("cg_paymethod p","l.zfid=p.id")
		->join("employee e","l.zfren=e.id")->where($where)->count();
			$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;





		$apply=Db::table("apti_zhifu_list")->alias("l")
		->join("cg_jxs j","l.jxs=j.id","LEFT")
		->join("gys g","l.gys=g.id","LEFT")
		->join("cg_paymethod p","l.zfid=p.id")
		->join("employee e","l.zfren=e.id")
		->where($where)->order("l.zfday desc")->limit($start,$list)
		->field("l.type,l.he_no,l.he_de_no,l.ap_no,l.ap_de_no,j.sn as jxs_no,j.title as jxs_name,g.no as gys_no,g.gys_name,l.zfday,l.pinzheng,p.title as zffs,l.weishui,l.shuijin,l.dikou,e.em_name")
		->select();
		$this->assign("apply",$apply);
		$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$datastr=json_encode($data);
		$this->assign("datastr",$datastr);
		return $this->fetch();
	}
	//导出支付
	public function daochuzf($datastr){
		$data=json_decode($datastr,true);
		
		$where="1=1 ";
		if($data){
			if(trim($data["jxs_no"])){
				$jxs_no=trim($data["jxs_no"]);
				$where.="and j.sn='{$jxs_no}' ";
			}
			if(trim($data["jxs_name"])){
				$jxs_name=trim($data["jxs_name"]);
				$where.="and j.title='{$jxs_name}' ";
			}
			if(trim($data["gys_no"])){
				$gys_no=trim($data["gys_no"]);
				$where.="and g.no='{$gys_no}' ";
			}
			if(trim($data["gys_name"])){
				$gys_name=trim($data["gys_name"]);
				$where.="and g.gys_name='{$gys_name}' ";
			}
			if(trim($data["he_no"])){
				$tmp=explode('_', trim($data["he_no"]));
				if(count($tmp)==1){
					$where.="and l.he_no='{$tmp[0]}' ";
				}else if(count($tmp)==2){
					$where.="and l.he_no='{$tmp[0]}' and l.he_de_no='{$tmp[1]}' ";
				}
			}
			if(trim($data["ap_no"])){
				$tmp=explode('_', trim($data["ap_no"]));
				if(count($tmp)==1){
					$where.="and l.ap_no='{$tmp[0]}' ";
				}else if(count($tmp)==2){
					$where.="and l.ap_no='{$tmp[0]}' and l.ap_de_no='{$tmp[1]}' ";
				}
			}
			if($data["pay"]){
				$where.="and l.zfid={$data["pay"]} ";
			}
			if($data["type"]){
				$where.="and l.type={$data["type"]} ";
			}
			if(trim($data["pinzheng"])){
				$pinzheng=trim($data["pinzheng"]);
				$where.="and l.pinzheng='{$pinzheng}' ";
			}
			if($data["start"]){
				$where.="and l.zfday>='{$data["start"]}' ";
			}
			if($data["end"]){
				$where.="and l.zfday<='{$data["end"]}' ";
			}
		}
		$this->assign("data",$data);
	

		$apply=Db::table("apti_zhifu_list")->alias("l")
		->join("cg_jxs j","l.jxs=j.id","LEFT")
		->join("gys g","l.gys=g.id","LEFT")
		->join("cg_paymethod p","l.zfid=p.id")
		->join("employee e","l.zfren=e.id")
		->where($where)->order("l.zfday desc")
		->field("l.type,l.he_no,l.he_de_no,l.ap_no,l.ap_de_no,j.sn as jxs_no,j.title as jxs_name,g.no as gys_no,g.gys_name,l.zfday,l.pinzheng,p.title as zffs,l.weishui,l.shuijin,l.dikou,e.em_name")
		->select();

		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("支付明细列表");

                 #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:O1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "支付明细列表");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:O2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);


         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getFill()->getStartColor()->setARGB('0099CCFF');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A3:O3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	    $objPHPExcel->getActiveSheet()->setCellValue('A3',"类型");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"核销明细编号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"原活动明细编号");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"经销商编号");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"经销商名称");      
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"供应商编号");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"供应商名称");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"支付类型");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"支付日期");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"凭证号");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"支付方式");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"支付未税金额");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"支付税金");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"是否增值税");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"支付人");
         $h=3;
        for($i=0;$i<count($apply);$i++){
        	$h++;$tmp=$apply[$i];
        	if($tmp["type"]==1)
        		$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"AP");
        	else
        		$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"TI");
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,"{$tmp["he_no"]}_{$tmp["he_de_no"]}");
	        $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,"{$tmp["ap_no"]}_{$tmp["ap_de_no"]}");
	        $objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$tmp["jxs_no"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$tmp["jxs_name"]);      
	        $objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$tmp["gys_no"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$tmp["gys_name"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('H'.$h,"支付");
	        $objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$tmp["zfday"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$tmp["pinzheng"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('K'.$h,$tmp["zffs"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('L'.$h,$tmp["weishui"]);
	        $objPHPExcel->getActiveSheet()->setCellValue('M'.$h,$tmp["shuijin"]);
	        if($tmp["dikou"]==1)
	        	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,"是");
	        else
	        	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,"否");
	        $objPHPExcel->getActiveSheet()->setCellValue('O'.$h,$tmp["em_name"]);


        }

         $hj=$h+1;
         $objPHPExcel->getActiveSheet()->getStyle($hj)->getAlignment()->setWrapText(true);
	    
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':O'.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':O'.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
	         //水平剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':O'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        //垂直剧中
	     $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':O'.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

	     $objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
	    $objPHPExcel->getActiveSheet()->mergeCells('A'.$hj.':K'.$hj);
	      $objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	     $objPHPExcel->getActiveSheet()->setCellValue('L'.$hj, '=SUM(L4:L'.$h.')');
	     $objPHPExcel->getActiveSheet()->setCellValue('M'.$hj, '=SUM(M4:M'.$h.')');

           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
           $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(30);





	        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:O'.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        


          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=支付明细列表.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
}